package com.email.crontab.common.jobs;

import com.beust.jcommander.internal.Lists;
import com.email.crontab.common.domain.MallConfigDO;
import com.email.crontab.common.domain.MallConfigItemDO;
import com.email.crontab.common.domain.SqlItemDO;
import com.email.crontab.common.service.MailService;
import com.email.crontab.framework.utils.DateFormatUtil;
import com.email.crontab.framework.utils.EasyPoiUtil;
import com.email.crontab.framework.utils.JSONHelper;
import com.email.crontab.mysql.service.MysqlService;
import com.email.crontab.oracle.service.OracleService;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import net.sf.json.JSONArray;
import org.apache.commons.lang3.StringUtils;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.StringWriter;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;


/**
 * Created by gumingjie on 2020-11-11.
 */
@Slf4j
@Component
@EnableScheduling
public class SqlJob {
    @Autowired
    private OracleService oracleService;
    @Autowired
    private MysqlService mysqlService;
    @Autowired
    private MailService mailService;
    @Value("${spring.mail.username}")
    private String formMail;
    @Value("${spring.mail.name}")
    private String userName;
    private static final String MALL_VM = "templates/mall.vm";

    @Scheduled(cron = "${jobs.sendGasJob.corn:0/60 * * * * ?}")
    private void executeInternal() throws Exception {
        // 获取json配置
        List<MallConfigDO> jsonConfig = this.getJsonConfig();
        for (MallConfigDO mallConfigDO : jsonConfig) {
            this.sendEmail(mallConfigDO);
        }


    }

    private List<MallConfigDO> getJsonConfig() {
        String test = JSONHelper.ResolveJsonFileToString("config.json");

        Map<String, Class> classMap = new HashMap<String, Class>();
        classMap.put("toList", MallConfigItemDO.class);
        classMap.put("ccList", MallConfigItemDO.class);
        classMap.put("sql", SqlItemDO.class);
        List<MallConfigDO> mallConfigDOS = JSONArray.toList(net.sf.json.JSONArray
                .fromObject(test), MallConfigDO.class, classMap);
        return mallConfigDOS;
    }

    private void sendEmail(MallConfigDO mallConfigDO) throws Exception {
        List<SqlItemDO> sql = mallConfigDO.getSql();
        String useDataSource = mallConfigDO.getUseDataSource();
        log.info("执行SQL: " + sql);
        long startTime = System.currentTimeMillis();
        List<List<Map<String, Object>>> dataList = Lists.newArrayList();
        List<String> fileNameList = Lists.newArrayList();
        for (SqlItemDO s : sql) {
            List<LinkedHashMap<String, Object>> lists;
            if ("oracle".equals(useDataSource)){
                lists = oracleService.oracle(s.getSqlName());
            }else {
                lists = mysqlService.mysql(s.getSqlName());
            }
            if (lists.size()==0){
                continue;
            }
            String fileName = EasyPoiUtil.exportBigExcel(s.getSqlContent(), lists);
            fileNameList.add(fileName);
            lists = null;
        }
        if (fileNameList.size()==0){
            log.info("今日无数据");
            return;
        }
        log.info("耗时：" + ((System.currentTimeMillis() - startTime) / 1000) + "s ");

        String subject = mallConfigDO.getSubject();
        String content = mallConfigDO.getContent();

        List<MallConfigItemDO> ccList = mallConfigDO.getCcList();
        List<MallConfigItemDO> toList = mallConfigDO.getToList();
        String now = DateFormatUtil.getFormateDate(new Date(), DateFormatUtil.DATE_PATTERN);


        // 生成excel 文件
//        Map<String, Object> data = new HashMap<>();
//        data.put("list", valueList);
//        File excelTemplate = this.getExcelTemplate(sqlName);
        // 主题 + 时间戳（yyyyMMddHHmmss） 为excel 名
        String sigleFileName = "";
        for (String fileName : fileNameList) {
            String[] splitFile = fileName.split(("/"));
            sigleFileName = sigleFileName + "" + splitFile[splitFile.length - 1];
        }

        // 写邮件模板
        StringWriter sw = new StringWriter();
        Map<String, Object> map = Maps.newHashMap();
        map.put("userName", userName);
        map.put("sendEmail", formMail);
        map.put("time", now);
        map.put("content", content);
        if (toList.size() > 0) {
            toList.get(0).setToName("收件人：" + toList.get(0).getToName());
        }
        map.put("toList", toList);
        if (ccList.size() > 0) {
            ccList.get(0).setToName("抄送人：" + ccList.get(0).getToName());
        }
        map.put("ccList", ccList);

        map.put("fileName", sigleFileName);
        map.put("subject", subject);
        VelocityContext context = new VelocityContext(map);
        Properties prop = new Properties();
        prop.put("file.resource.loader.class", "org.apache.velocity.runtime.resource.loader.ClasspathResourceLoader");
        Velocity.init(prop);
        Template tpl = Velocity.getTemplate(MALL_VM, StandardCharsets.UTF_8.name());
        tpl.merge(context, sw);

        List<String> toListMail = toList.stream().map(MallConfigItemDO::getToMail).filter(StringUtils::isNotBlank).collect(Collectors.toList());
        String[] toArray = toListMail.toArray(new String[0]);

        List<String> ccListMail = ccList.stream().map(MallConfigItemDO::getToMail).filter(StringUtils::isNotBlank).collect(Collectors.toList());
        String[] ccArray = ccListMail.toArray(new String[0]);

        String html = sw.toString();
        mailService.sendAttachmentsMail(toArray, ccArray, subject, html, fileNameList);
        System.gc();
    }

    private File getExcelTemplate(String filename) throws FileNotFoundException {
        File file = new File(filename);
        if (!file.exists()) { // 如果同级目录没有，则去config下面找
            log.debug("不在同级目录，进入config目录查找");
            file = new File("config/" + filename);
        }
        Resource resource = new FileSystemResource(file);
        if (!resource.exists()) { //config目录下还是找不到，那就直接用classpath下的
            log.debug("不在config目录，进入classpath目录查找");
            file = ResourceUtils.getFile("classpath:" + filename);
        }
        return file;
    }
//    public static String getAcctMonth() {
//        Calendar instance = Calendar.getInstance();
//        int i = instance.get(Calendar.MONTH);
//        String s = String.valueOf(i);
//        // 保留num的位数
//        // 0代表前面补充0
//        // num 代表长度为4
//        // d 代表参数为正数型
//        return String.format("%0" + 2 + "d", Integer.parseInt(s) + 1);
//    }
}
